package cbh.tool;

import java.sql.*;
import java.util.Objects;

/**
 * Created by huxin on 2015/12/26.
 */
public class Test {

    public static final String url = "jdbc:mysql://127.0.0.1/sync_from_gs?useUnicode=true&characterEncoding=UTF-8";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "root";
    public static final String password = "root";
    private static String LOCALORG = "500113";
    private static String LOCALORGNAME = "巴南区";
    private static double PERCENT = 0;
    private static double totalQuantity = 0;
    private static double totalCapital = 0;
    private static double totalPerson = 0;

    public static Connection conn = null;
    public static Statement statement = null;

    public static void set() {
        try {
            Class.forName(name);
            if (Objects.isNull(conn)) {
                conn = DriverManager.getConnection(url, user, password);
            }
            statement = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void close() {
        try {
            conn.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        set();

        String[] dname = {"巴南区","北碚区","璧山区","长寿区","城口县","大渡口区","大足区",
                "垫江县","丰都县","奉节县","涪陵区","合川区","江北区","江津区",
                "九龙坡区","开县","梁平县","两江新区","南岸区","南川区","彭水苗族土家族自治县",
                "綦江区","黔江区","荣昌区","沙坪坝区","石柱土家族自治县","潼南区","铜梁区",
                "万州区","巫山县","巫溪县","武隆县","秀山土家族苗族自治县","永川区","酉阳土家族苗族自治县",
                "渝北区","渝中区","云阳县","忠县"};
        String[] lname = {"500113","500109","500227","500221","500229","500104","500225",
                "500231","500230","500236","500102","500382","500105","500381",
                "500107","500234","500228","500905","500108","500384","500243",
                "500222","500900","500226","500106","500240","500223","500224",
                "500101","500237","500238","500232","500241","500383","500242",
                "500112","500103","500235","500233"};
        double[] percents = {0.305, 0.31, 0.2149, 0.1335, 0.0258, 0.326, 0.2876,
                0.17, 0.023, 0.06, 0.117, 0.035, 0.5273, 0.097,
                0.358, 0.13, 0.0616, 0.157, 0.427, 0.1566, 0.13,
                0.113, 0.03, 0.127, 0.4482, 0.065, 0.0552, 0.032,
                0.132, 0.2, 0.0167, 0.132, 0.029, 0.1302, 0.02,
                0.315, 0.215, 0.0228, 0.0506};
        for(int i = 0; i < dname.length; i++) {
            LOCALORGNAME = dname[i];
            LOCALORG = lname[i];
            PERCENT = percents[i];
            String quantity = target("1");
            String appendQuantity = appendTarget("1");
            String capital = target("regcap");
            String appendCapital = appendTarget("regcap");
            String person = target("an_emp_num");
            String appendPerson = appendTarget("an_emp_num");
            String result = "####"+LOCALORGNAME+" "+LOCALORG+"\n" +
                    "api.test=/api/data?type=quantity&district=" + LOCALORGNAME + "\n" +
                    "data_get.test={\"data\":{\"target\":{ " +
                    "\"quantity\":" + quantity + ", " +
                    "\"appendQuantity\":" + appendQuantity + "," +
                    "\"capital\":" + capital + ", " +
                    "\"appendCapital\":" + appendCapital + "," +
                    "\"person\":" + person + ", " +
                    "\"appendPerson\":" + appendPerson + "" +
                    "}, " +
                    "\"graphs\":{ " +
                    "\"monthTrends\":" + monthQuantityTrends("1") + ", " +
                    "\"yearTrends\":" + yearQuantityTrends("1") + ", " +
                    "\"appendDistribute\":" + appendQuantityDistribute("1") + ", " +
                    "\"appendYear\":" + appendQuantityYear("1") + ", " +
                    "\"industryType\":" + industryQuantityType("1") + ", " +
                    "\"appendIndustryType\":" + appendIndustryQuantityType("1") + "" +
                    "}}}\n" +


                    "api.test=/api/data?type=capital&district=" + LOCALORGNAME + "\n" +
                    "data_get.test={\"data\":{\"target\":{ " +
                    "\"quantity\":" + quantity + ", " +
                    "\"appendQuantity\":" + appendQuantity + "," +
                    "\"capital\":" + capital + ", " +
                    "\"appendCapital\":" + appendCapital + "," +
                    "\"person\":" + person + ", " +
                    "\"appendPerson\":" + appendPerson + "" +
                    "}, " +
                    "\"graphs\":{ " +
                    "\"monthTrends\":" + monthQuantityTrends("regcap") + ", " +
                    "\"yearTrends\":" + yearQuantityTrends("regcap") + ", " +
                    "\"appendDistribute\":" + appendQuantityDistribute("regcap") + ", " +
                    "\"appendYear\":" + appendQuantityYear("regcap") + ", " +
                    "\"industryType\":" + industryQuantityType("regcap") + ", " +
                    "\"appendIndustryType\":" + appendIndustryQuantityType("regcap") + "" +
                    "}}}\n" +


                    "api.test=/api/data?type=person&district=" + LOCALORGNAME + "\n" +
                    "data_get.test={\"data\":{\"target\":{ " +
                    "\"quantity\":" + quantity + ", " +
                    "\"appendQuantity\":" + appendQuantity + "," +
                    "\"capital\":" + capital + ", " +
                    "\"appendCapital\":" + appendCapital + "," +
                    "\"person\":" + person + ", " +
                    "\"appendPerson\":" + appendPerson + "" +
                    "}, " +
                    "\"graphs\":{ " +
                    "\"monthTrends\":" + monthQuantityTrends("an_emp_num") + ", " +
                    "\"yearTrends\":" + yearQuantityTrends("an_emp_num") + ", " +
                    "\"appendDistribute\":" + appendQuantityDistribute("an_emp_num") + ", " +
                    "\"appendYear\":" + appendQuantityYear("an_emp_num") + ", " +
                    "\"industryType\":" + industryQuantityType("an_emp_num") + ", " +
                    "\"appendIndustryType\":" + appendIndustryQuantityType("an_emp_num") + "" +
                    "}}}";
            System.out.println(result);
//            System.out.println(LOCALORGNAME + ": quantity { " +quantity+ "},capital { " +capital+ "},person { " +person+ "}");
            totalQuantity = totalQuantity + Double.valueOf(quantity);
            totalCapital = totalCapital + Double.valueOf(capital);
            totalPerson = totalPerson + Double.valueOf(person);
        }
        System.out.println("totalQuantity { " +totalQuantity+ "},totalCapital { " +totalCapital+ "},totalPerson { " +totalPerson+ "}");
    }

    private static String appendTarget(String flag) {
        String sql = "select round(sum(" + flag + ") * "+PERCENT+")  num from gs_entbase " +
                "where `ESTDATE` <= '2017-05-01' " +
                "and `ESTDATE` >= '20017-01-01' " +
                "and LOCALORG = '" + LOCALORG + "' " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALADM is not null and LOCALADM <> '500000' ";
        try {
            ResultSet set = statement.executeQuery(sql);
            set.next();
            String resule = set.getString("num");
            set.close();
            return resule;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "1";
    }

    private static String target(String flag) {
        String sql = "select round(sum(" + flag + ") * "+PERCENT+") num from gs_entbase " +
                "where `ESTDATE` <= '2017-05-01' " +
                "and `ESTDATE` >= '2007-05-01' " +
                "and LOCALORG = '" + LOCALORG + "' " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALADM is not null and LOCALADM <> '500000' ";
        try {
            ResultSet set = statement.executeQuery(sql);
            set.next();
            String resule = set.getString("num");
            set.close();
            return resule;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "1";
    }

    private static String appendIndustryQuantityType(String flag) {

        String sql = "select round(a.num * " +PERCENT+ ") `value`,b.CODENAME `name` from " +
                "( " +
                "select INDUSTRYPHY,sum(" + flag + ") num from gs_entbase " +
                "where `ESTDATE` <= now() " +
                "and `ESTDATE` >= '2017-01-01' " +
                " " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALORG = '" + LOCALORG + "' " +
                "and INDUSTRYCO is not null and INDUSTRYPHY is not null group by INDUSTRYPHY " +
                ") a " +
                "left join " +
                "( " +
                "select * from gs_c_tree where PARENTID = 0 and CODETYPE = 'CA05' " +
                ") b on a.INDUSTRYPHY = b.levelCode " +
                "order by a.num desc";
        return getString(sql);
    }

    private static String industryQuantityType(String flag) {
        String sql = "select round(a.num * " +PERCENT+ ") `value`,b.CODENAME `name` from " +
                "( " +
                "select INDUSTRYPHY,sum(" + flag + ") num from gs_entbase " +
                "where `ESTDATE` <= now() " +
                " " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALORG = '" + LOCALORG + "' " +
                "and INDUSTRYCO is not null and INDUSTRYPHY is not null group by INDUSTRYPHY " +
                ") a " +
                "left join " +
                "( " +
                "select * from gs_c_tree where PARENTID = 0 and CODETYPE = 'CA05' " +
                ") b on a.INDUSTRYPHY = b.levelCode " +
                "order by a.num desc";
        return getString(sql);
    }

    private static String appendQuantityYear(String flag) {
        String sql = "select date_format(ESTDATE,'%Y-%m') `name`, round(count(1) * " +PERCENT+ ") `value` , date_format(ESTDATE,'%m') `month` from gs_entbase " +
                "            where date_format(ESTDATE,'%Y') in ('2014','2015','2016') " +
                " " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALORG = '" + LOCALORG + "' " +
                "            group by date_format(ESTDATE,'%Y-%m') order by date_format(ESTDATE,'%m'),date_format(ESTDATE,'%Y')";
        StringBuilder sb = new StringBuilder();
        try {
            ResultSet set = statement.executeQuery(sql);
            sb.append("[");
            int month = 0;
            while (set.next()) {
                if (month == 0) {
                    sb.append("[");
                }
                sb.append("{\"name\":\"" + set.getString("name") + "\",\"value\":" + set.getString("value") + "},");
                if (month == 2 || (set.getInt("month") >= 8 && month == 1)) {
                    sb = new StringBuilder(sb.substring(0, sb.length() - 1));
                    sb.append("],");
                    month = -1;
                }
                month++;
            }
            sb = new StringBuilder(sb.substring(0, sb.length() - 2));
            sb.append("}]],");
            set.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sb.substring(0, sb.length() - 1).toString();
    }

    private static String appendQuantityDistribute(String flag) {
        String sql = "select round(a.num * " +PERCENT+ ") `value`,b.UNITNAME `name` from " +
                "(select LOCALADM,sum(" + flag + ") num from gs_entbase " +
                "where `ESTDATE` <= '2017-05-01' " +
                "and `ESTDATE` >= '2007-05-01' " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALORG = '" + LOCALORG + "' " +
                "and LOCALADM is not null and LOCALADM <> '500000'  " +
                "group by LOCALADM) a " +
                "left join " +
                "gs_zcdk_unitinfo b " +
                "on a.LOCALADM = b.unitId group by b.UNITNAME order by a.num desc";
        return getString(sql);
    }

    private static String yearQuantityTrends(String flag) {
        String sql = " " +
                "select round(sum(" + flag + ") * " +PERCENT+ ") `value`,g.`amonth` `name` from " +
                "( " +
                "select *,DATE_FORMAT(ESTDATE,'%Y') `amonth` from gs_entbase where  " +
                "DATE_FORMAT(`ESTDATE`,'%Y') <= DATE_FORMAT('2017-05-01','%Y') " +
                "and DATE_FORMAT(`ESTDATE`,'%Y') >= DATE_FORMAT('2007-05-01','%Y')  " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALORG = '" + LOCALORG + "' " +
                ") g group by g.`amonth` order by g.`amonth` ";
        return getString(sql);
    }

    private static String monthQuantityTrends(String flag) {
        String sql = " " +
                "select round(sum(" + flag + ") * " +PERCENT+ ") `value` ,g.`amonth` `name` from " +
                "( " +
                "select *,DATE_FORMAT(ESTDATE,'%Y-%m') `amonth` from gs_entbase where  " +
                "DATE_FORMAT(`ESTDATE`,'%Y-%m') <= DATE_FORMAT('2017-05-01','%Y-%m') " +
                "and scale_type is not null and scale_type > 0 " +
                "and LOCALORG = '" + LOCALORG + "' " +
                "and DATE_FORMAT(`ESTDATE`,'%Y-%m') >= DATE_FORMAT('2016-05-01','%Y-%m')  " +
                ") g group by g.`amonth` order by g.`amonth` ";
        return getString(sql);
    }

    private static String getString(String sql) {
        StringBuilder sb = new StringBuilder();
        try {
            ResultSet set = statement.executeQuery(sql);
            sb.append("[");
            while (set.next()) {
                sb.append("{\"name\":\"" + set.getString("name") + "\",\"value\":" + set.getString("value") + "},");
            }
            sb = new StringBuilder(sb.substring(0, sb.length() - 1));
            sb.append("],");
            set.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sb.substring(0, sb.length() - 1).toString();
    }

    /*public static void main(String[] args) throws IOException, ToolBaseException {
        File file1 = new File("/home/hx-pc/IdeaProjects/CatenaFramework/data/tem.json");
        File file2 = new File("/home/hx-pc/IdeaProjects/CatenaFramework/data/parkList.json");
        InputStreamReader isr1 = new InputStreamReader(new FileInputStream(file1), "utf-8");
        BufferedReader br1 = new BufferedReader(isr1);
        InputStreamReader isr2 = new InputStreamReader(new FileInputStream(file2), "utf-8");
        BufferedReader br2 = new BufferedReader(isr2);
        StringBuilder linestr1 = new StringBuilder();
        while (true) {
            String s = br1.readLine();
            if (s != null) {
                linestr1 = linestr1.append(s);
            } else
                break;
        }

        StringBuilder linestr2 = new StringBuilder();
        while (true) {
            String s = br2.readLine();
            if (s != null) {
                linestr2 = linestr2.append(s);
            } else
                break;
        }

        List<LinkedHashMap> list1 =  new ObjectMapper().readValue(linestr1.toString().getBytes(), List.class);
        List<LinkedHashMap> list2 =  new ObjectMapper().readValue(linestr2.toString().getBytes(), List.class);
       for (int i = 0; i< list1.size() ;i ++) {
           list1.get(i).put("reportId",getId((String) list1.get(i).get("name"), list2));
       }
        List result = new ArrayList<>();
        Set<String> set = new HashSet<>();
        for(int i = 0 ; i < list1.size(); i++) {
            if(!StringUtils.isEmpty(list1.get(i).get("reportId"))){
                int old = set.size();
                set.add((String) list1.get(i).get("name"));
                if (set.size() != old) {
                    result.add(list1.get(i));
                }
            }
        }
        System.out.println(new ObjectMapper().writeValueAsString(result));
        br1.close();
        br2.close();
    }

    private static String getId(String name, List<LinkedHashMap> list) {
        for (LinkedHashMap linkedHashMap : list) {
            if (name.equals(linkedHashMap.get("corporation"))
                    || linkedHashMap.get("corporation").equals("重庆烯奇新材料技术有限公司")
                    || linkedHashMap.get("corporation").equals("重庆品之绅文化传播有限公司")
                    || linkedHashMap.get("corporation").equals("重庆睿兴会计服务有限公司")
                    || linkedHashMap.get("corporation").equals("重庆弗而文化传播有限公司")
                    || linkedHashMap.get("corporation").equals("重庆南刊文化传播有限公司")
                    || linkedHashMap.get("corporation").equals("重庆市大渡口区优享源电子商务服务部")
                    || linkedHashMap.get("corporation").equals("重庆晓仙电子商务有限公司")
                    || linkedHashMap.get("corporation").equals("重庆春芳信息技术有限公司")
                    || linkedHashMap.get("corporation").equals("重庆荧光文化传播有限责任公司")
                    || linkedHashMap.get("corporation").equals("重庆虹姐五化电子商务有限公司")
                    || linkedHashMap.get("corporation").equals("重庆市大渡口区南溟雕塑有限责任公司")) {
                return (String) linkedHashMap.get("id");
            }
        }
        return null;
    }*/

    private static int aa(int a) {

        a--;
        if (a > 0) {
            return aa(a);
        } else {
            return 1;
        }
    }


    /*public static void main(String[] args) throws IOException {
        *//*File file1 = new File("/home/hx-pc/project/tools.1.0/src/main/resources/a.json");
        File file2 = new File("/home/hx-pc/project/tools.1.0/src/main/resources/b.json");
        InputStreamReader isr = new InputStreamReader(new FileInputStream(file1), "utf-8");
        BufferedReader br = new BufferedReader(isr);
        List<LinkedHashMap> myBeans = new ObjectMapper().readValue(br.readLine(), List.class);

        InputStreamReader isr1 = new InputStreamReader(new FileInputStream(file2), "utf-8");
        BufferedReader b1r = new BufferedReader(isr1);
        List<LinkedHashMap> toBs = new ObjectMapper().readValue(b1r.readLine(), List.class);
        br.close();
        b1r.close();*//*
        *//*List<Integer> arrayLists = new ArrayList<>();
        arrayLists.add(0);
        arrayLists.add(1);
        int key = 3;
        while (true) {
            if (key <= 0) {
                break;
            } else {
                arrayLists.add(arrayLists.get(arrayLists.size()-2) + arrayLists.get(arrayLists.size()-1));
                System.out.println(arrayLists);
            }
            key--;
        }
        System.out.println(arrayLists.get(arrayLists.size()-1));*//*
        List<List<String>> ts = new ArrayList<>();
        File file1 = new File("/home/hx-pc/下载/results.csv");
        FileInputStream fis = new FileInputStream(file1);
        BufferedReader br = new BufferedReader(new ReaderUTF8(fis));
        String line = br.readLine();
        line = br.readLine();
        while (line != null) {
            List<String> t = new ArrayList<>();
            Collections.addAll(t, line.split(","));
            ts.add(t);
            line = br.readLine();
        }
        System.out.println(ts);
        fis.close();
        set();
        File file = new File("/home/hx-pc/下载/梦工场企业资料整理/梦工场企业资料整理文档.xlsx");
        try {
            List<MyBean> list = XLSFactory.buildAnalyze().analyzeWithoutRowAndColumn(file, MyBean.class, 0, -1);
            list = list.stream()
                    .filter(myBean -> myBean.getName() != null)
                    .collect(Collectors.toList());
            list.forEach(myBean -> {
                myBean.setWechat(myBean.getId() + "/wechat/");
                myBean.setVideo(myBean.getId() + "/video/");
                myBean.setCompanyIntroduction(myBean.getId() + "/companyIntroduction/");
                myBean.setProductPresentation(myBean.getId() + "/productPresentation/");
                myBean.setProductCertification(myBean.getId() + "/productCertification/");
                myBean.setTrademarkCertificate(myBean.getId() + "/trademarkCertificate/");
                myBean.setIntellectualProperty(myBean.getId() + "/intellectualProperty/");
                myBean.setLogo(myBean.getId() + "/logo/");
                myBean.setScores(getScore(myBean.getName(), ts));
                myBean.setRegCap(getRegCap(myBean.getName()));
                myBean.setFloor(getFloor(myBean.getAddress()));
                myBean.setRoomNum(getRoomNum(myBean.getAddress()));
                System.out.println(myBean.getName());
            });
            close();
            System.out.println(new ObjectMapper().writeValueAsString(list));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ToolBaseException e) {
            e.printStackTrace();
        }
    }

    private static Integer getRoomNum(String address) {
        if (address.contains("工位")) {
            return Integer.valueOf(address.substring(2, address.indexOf("号")));
        } else {
            return Integer.valueOf(address.substring(address.indexOf("楼") + 1, address.indexOf("号")));
        }
    }

    private static Integer getFloor(String address) {
        if (address.contains("工位")) {
            return 3;
        } else {
            return Integer.valueOf(address.substring(0, address.indexOf("楼")));
        }
    }

    private static Double getRegCap(String name) {
        Double result = new Double(0.0);
        String sql = "select REGCAP from gs_entbase where entname ='" + name + "'";
        ResultSet set;
        try {
            set = statement.executeQuery(sql);
            while (set.next()) {
                result = set.getDouble("REGCAP");
            }
            set.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    private static Map<String, List<Integer>> getScore(String name, List<List<String>> ts) {
        Map<String, List<Integer>> map = new HashMap<>();
        List<Integer> allScore = new ArrayList<>();
        List<Integer> secondScore = new ArrayList<>();
        List<Integer> firstScore = new ArrayList<>();
        for (int i = 0; i < ts.size(); i++) {
            if (ts.get(i).get(0).equals(name)) {
                allScore.addAll(ts.get(i).subList(1, ts.get(i).size()).stream().map(s -> Integer.valueOf(s)).collect(Collectors.toList()));
                ts.remove(i);
                break;
            }
        }
        Integer second = 0;
        Integer oldN = 0;
        for (int n = 0; n < allScore.size(); n++) {
            second += allScore.get(n);
            if (n == 6 || n == 15 || n == 19 || n == 23 || n == 27) {
                secondScore.add(second / (n - oldN));
                oldN = n;
                second = 0;
            }
        }
        Integer first = 0;
        for (Integer score : secondScore) {
            first += score;
        }
        firstScore.add(first / 5);
        map.put("allScore", allScore);
        map.put("secondScore", secondScore);
        map.put("firstScore", firstScore);
        return map;
    }*/

}


/*
File file = new File("/home/hx-pc/下载/梦工场企业资料整理/梦工场企业资料整理文档.xlsx");
        try {
            List<MyBean> list = XLSFactory.buildAnalyze().analyzeWithoutRowAndColumn(file, MyBean.class, 0, -1);
            list = list.stream()
                .filter(myBean -> myBean.getName() != null)
                .collect(Collectors.toList());
            list.forEach(myBean -> {
                myBean.setStaticCode(Tool.getRandom(5));
                myBean.setWechat(myBean.getId()+"/wechat/");
                myBean.setVideo(myBean.getId()+"/video/");
                myBean.setCompanyIntroduction(myBean.getId()+"/companyIntroduction/");
                myBean.setProductPresentation(myBean.getId()+"/productPresentation/");
                myBean.setProductCertification(myBean.getId()+"/productCertification/");
                myBean.setTrademarkCertificate(myBean.getId()+"/trademarkCertificate/");
                myBean.setIntellectualProperty(myBean.getId()+"/intellectualProperty/");
                myBean.setLogo(myBean.getId()+"/logo/");
            });
            System.out.println(new ObjectMapper().writeValueAsString(list));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ToolBaseException e) {
            e.printStackTrace();
        }
 */